package com.lg.startertemplate.controller;

import com.lg.startertemplate.dao.DaoCenter;
import com.lg.startertemplate.dao.impl.UserDao;
import com.lg.startertemplate.dao.jdbc.DbHelper;
import com.lg.startertemplate.dao.jdbc.PgDbHelper;
import com.lg.startertemplate.domain.TUser;
import com.lg.startertemplate.exception.CommonException;
import io.swagger.annotations.Api;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

/**
 * 数据的操作
 * 曾 删 改 查
 * <p>
 * 表的操作
 * 创建表 查询表是否存在 修改表的名称 删除表
 * 库的操作
 * 创建库 查询库是否存在 修改库的名称 删除库
 */
@Slf4j
@RestController
@Api(tags = "Jdbc数据的操作 表的操作 库的操作")
public class JdbcController {

    @Autowired
    private UserDao userDao;

    @Autowired
    DaoCenter daoCenter;

    @Autowired
    private PgDbHelper dbHelper;

    //    -------------------------------数据操作-------------------------
    @GetMapping("insertUser")
    public Boolean insertUser(@RequestParam(defaultValue = "defaultDatasource") String datasourceName) {

        String sql = "insert into t_user(age,map_default,name) values (49, 9, 'gg')";
        if (0 == dbHelper.insert(datasourceName, sql))
            return true;

        return false;
    }


    @GetMapping("queryUser")
    public List<TUser> queryUser(@RequestParam(defaultValue = "defaultDatasource") String datasourceName) {
        List<TUser> list = new ArrayList<>();
        String sql = "select * from t_user";
        list.addAll(dbHelper.query(datasourceName, sql, rs -> {
            return TUser.getUsers(rs);
        }));
        return list;
    }

    @GetMapping("updateUser")
    public Boolean updateUser(@RequestParam(defaultValue = "defaultDatasource") String datasourceName, Long updateId) {

        String sql = "update t_user set name = 'updateName999' where id =" + updateId;
        if (0 == dbHelper.update(datasourceName, sql))
            return true;

        return false;
    }

    @GetMapping("deleteUser")
    public Boolean deleteUser(@RequestParam(defaultValue = "defaultDatasource") String datasourceName, Long updateId) {

        String sql = "delete from t_user where id = " + updateId;
        if (0 == dbHelper.delete(datasourceName, sql))
            return true;

        return false;
    }


//    -------------------------------表的操作-------------------------

    //============================创建表============================
    //在spring-data jpa中 使用query, 非常的不推荐
    @GetMapping("queryTable")
    public void queryTable() {
        userDao.createTable("test_table");
    }

    //如果项目中只有一个数据源且使用的持久化框架是spring-data jpa么, 非常推荐使用entityManager执行自定义的sql
    @GetMapping("queryManagerCreateTable")
    @Transactional
    public void queryManagerCreateTable() {
        try {
            String createSql = "create table \"public\".\"" + new Random().nextInt(10) + "_table\"(\"id\" SERIAL primary key,\"create_time\" timestamp(6),\"update_time\" timestamp(6));";
            log.info("创建数据库 " + createSql);
            daoCenter.getEntityManager().createNativeQuery(createSql).executeUpdate();
        } catch (Exception e) {
            log.error("创建数据库出错", e);
        }
    }

    //如果项目中有多个数据源 或者没有使用任何持久化框架的话, 自己封装数据源和jdbc
    @GetMapping("jdbcCreateTable")
    public String jdbcCreateTable(@RequestParam(defaultValue = "defaultDatasource") String datasourceName) {
        int randomNum = new Random().nextInt(10);

        String sql = "create table \"public\".\"" + randomNum + "_table\"(\"id\" SERIAL primary key,\"create_time\" timestamp(6),\"update_time\" timestamp(6));";
        dbHelper.createTable(datasourceName, sql);

        return randomNum + "_table";
    }

    //============================pg表 查询表是否存在============================
    @GetMapping("jdbcExitTable")
    public Boolean jdbcExitTable(@RequestParam(defaultValue = "defaultDatasource") String datasourceName, String tableName) {
        String sql = "select count(*) from pg_class where relname = '" + tableName + "'";
        return dbHelper.existTable(datasourceName, sql);
    }


    //============================修改表的名称============================
    @GetMapping("jdbcUpdateTable")
    public String jdbcUpdateTable(@RequestParam(defaultValue = "defaultDatasource") String datasourceName, String tableName) {
        String reName = new Random().nextInt(10) + "_table";
        String sql = "alter table \"" + tableName + "\"" + " rename to \"" + reName + "\"";
        dbHelper.updateTable(datasourceName, sql);
        return reName;
    }

    //============================删除表这个表============================
    @GetMapping("jdbcDeleteTable")
    public Integer jdbcDeleteTable(@RequestParam(defaultValue = "defaultDatasource") String datasourceName, String tableName) {
        String sql = "drop table  \"" + tableName + "\"";
        return dbHelper.deleteTable(datasourceName, sql);
    }


    //    -------------------------------库的操作-------------------------
    @GetMapping("jdbcCreateDatabase")
    public String jdbcCreateDatabase(@RequestParam(defaultValue = "defaultDatasource") String datasourceName) {
        String dbName = new Random().nextInt(10) + "_db";
        if (jdbcExitDatabase(datasourceName, dbName)) {
            throw new CommonException(dbName + " 此数据库已经存在");
        } else {
            String sql = "create database \"" + dbName + "\"";
            dbHelper.createTable(datasourceName, sql);
        }

        return dbName;
    }


    @GetMapping("jdbcExitDatabase")
    public Boolean jdbcExitDatabase(@RequestParam(defaultValue = "defaultDatasource") String datasourceName, String dbName) {
        String sql = "select datname  from pg_catalog.pg_database  where datname= '" + dbName + "'";
        return dbHelper.existDatabase(datasourceName, sql);
    }

    public String updateDatabase(String datasourceName, String dbName, int runTotal) {
        String reName = "update_db";
        //校验 reName_db是否存在
        String sql = "alter database \"" + dbName + "\"" + " rename to \"" + reName + "\"";

        String cleanSql = "select pg_terminate_backend(pid) from pg_stat_activity where datname= \'" + dbName + "\'";
        if (jdbcExitDatabase(datasourceName, dbName)) {
            try {
                dbHelper.updateDatabase(datasourceName, sql);
                return "update_db";
            } catch (Exception e) {
                if (runTotal++ > 1) {
                    throw new CommonException(dbName + " 此数据库繁忙");
                } else {
                    dbHelper.run(datasourceName, cleanSql);
                    return this.updateDatabase(datasourceName, dbName, runTotal);
                }
            }
        } else {
            throw new CommonException(dbName + " 此数据库不存在");
        }

    }

    @GetMapping("jdbcUpdateDatabase")
    public String jdbcUpdateDatabase(@RequestParam(defaultValue = "defaultDatasource") String datasourceName, String dbName) {
        return updateDatabase(datasourceName, dbName, 0);
    }

    public Integer deleteDatabase(String datasourceName, String dbName, int runTotal) {
        String sql = "drop database  \"" + dbName + "\"";
        String cleanSql = "select pg_terminate_backend(pid) from pg_stat_activity where datname= \'" + dbName + "\'";

        if (jdbcExitDatabase(datasourceName, dbName)) {
            try {
                return dbHelper.deleteDatabase(datasourceName, sql);
            } catch (Exception e) {
                if (++runTotal > 1) {
                    throw new CommonException(dbName + " 此数据库繁忙");
                } else {
                    dbHelper.run(datasourceName, cleanSql);
                    return this.deleteDatabase(datasourceName, dbName, runTotal);
                }
            }
        } else {
            throw new CommonException(dbName + " 此数据库不存在");
        }

    }

    @GetMapping("jdbcDeleteDatabase")
    public Integer jdbcDeleteDatabase(@RequestParam(defaultValue = "defaultDatasource") String datasourceName, String dbName) {
        return this.deleteDatabase(datasourceName, dbName,0);
    }

}
